const pool = require('../models/db');
const stockService = require('./stockService');

// 获取销售信息
const getSales = async (query) => {
  const { sales_id, goods_id, goods_name, sales_price, sales_quantity, sales_time, remarks } = query;

  let sqlQuery = 'SELECT s.*, g.goods_name FROM sales s JOIN goods g ON s.goods_id = g.goods_id';
  let conditions = [];
  let values = [];

  if (sales_id) {
    conditions.push('s.sales_id = $' + (conditions.length + 1));
    values.push(sales_id);
  }
  if (goods_id) {
    conditions.push('s.goods_id = $' + (conditions.length + 1));
    values.push(goods_id);
  }
  if (goods_name) {
    conditions.push('g.goods_name ILIKE $' + (conditions.length + 1));
    values.push(`%${goods_name}%`);
  }
  if (sales_price) {
    conditions.push('s.sales_price = $' + (conditions.length + 1));
    values.push(sales_price);
  }
  if (sales_quantity) {
    conditions.push('s.sales_quantity = $' + (conditions.length + 1));
    values.push(sales_quantity);
  }
  if (sales_time) {
    // 修复时差问题
    const startTime = new Date(new Date(sales_time[0]).getTime() + 8 * 3600 * 1000).toISOString();
    const endTime = new Date(new Date(sales_time[1]).getTime() + 8 * 3600 * 1000).toISOString();
    conditions.push('s.sales_time BETWEEN $' + (conditions.length + 1) + ' AND $' + (conditions.length + 2));
    values.push(startTime, endTime);
  }
  if (remarks) {
    conditions.push('s.remarks ILIKE $' + (conditions.length + 1));
    values.push(`%${remarks}%`);
  }

  if (conditions.length > 0) {
    sqlQuery += ' WHERE ' + conditions.join(' AND ');
  }

  const results = await pool.query(sqlQuery, values);
  return { items: results.rows, total: results.rowCount };
};

// 新建销售信息
const addSales = async (data) => {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    const { goods_name, sales_price, sales_quantity, sales_time, remarks } = data;
    const goodsResult = await client.query('SELECT goods_id FROM goods WHERE goods_name = $1', [goods_name]);
    if (goodsResult.rowCount === 0) {
      throw new Error('No goods found with this name');
    }
    const goods_id = goodsResult.rows[0].goods_id;

    const results = await client.query(
      'INSERT INTO sales (goods_id, sales_price, sales_quantity, sales_time, remarks) VALUES ($1, $2, $3, $4, $5) RETURNING *',
      [goods_id, sales_price, sales_quantity, new Date(new Date(sales_time).getTime() + 8 * 3600 * 1000).toISOString(), remarks || null]
    );
    await stockService.updateStockByTrans(client, goods_id, -sales_quantity);
    await client.query('COMMIT');
    return results.rows[0];
  } catch (error) {
    await client.query('ROLLBACK');
    throw error;
  } finally {
    client.release();
  }
};

// 更新销售信息
const updateSales = async (id, data) => {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    const { goods_name, sales_price, sales_quantity, sales_time, remarks } = data;
    const goodsResult = await client.query('SELECT goods_id FROM goods WHERE goods_name = $1', [goods_name]);
    if (goodsResult.rowCount === 0) {
      throw new Error('No goods found with this name');
    }
    const goods_id = goodsResult.rows[0].goods_id;

    const oldSales = await client.query('SELECT * FROM sales WHERE sales_id = $1', [id]);
    if (oldSales.rowCount === 0) {
      throw new Error('No sales found with this ID');
    }
    const oldQuantity = oldSales.rows[0].sales_quantity;

    const results = await client.query(
      'UPDATE sales SET goods_id = $1, sales_price = $2, sales_quantity = $3, sales_time = $4, remarks = $5 WHERE sales_id = $6 RETURNING *',
      [goods_id, sales_price, sales_quantity, new Date(new Date(sales_time).getTime() + 8 * 3600 * 1000).toISOString(), remarks || null, id]
    );
    await stockService.updateStockByTrans(client, goods_id, oldQuantity - sales_quantity);
    await client.query('COMMIT');
    return results.rows[0];
  } catch (error) {
    await client.query('ROLLBACK');
    throw error;
  } finally {
    client.release();
  }
};

// 删除销售信息
const deleteSales = async (id) => {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    const oldSales = await client.query('SELECT * FROM sales WHERE sales_id = $1', [id]);
    if (oldSales.rowCount === 0) {
      throw new Error('No sales found with this ID');
    }
    const oldQuantity = oldSales.rows[0].sales_quantity;

    const results = await client.query('DELETE FROM sales WHERE sales_id = $1 RETURNING *', [id]);
    await stockService.updateStockByTrans(client, oldSales.rows[0].goods_id, oldQuantity);
    await client.query('COMMIT');
    if (results.rowCount === 0) {
      throw new Error('No sales found with this ID');
    }
  } catch (error) {
    await client.query('ROLLBACK');
    throw error;
  } finally {
    client.release();
  }
};

// 获取销售信息建议
const getSuggestions = async (query) => {
  const { field, query: searchString } = query;
  let results;
  if (field === 'goods_name') {
    results = await pool.query(
      `SELECT DISTINCT ${field} FROM goods WHERE ${field} ILIKE $1`,
      [`%${searchString}%`]
    );
  } else {
    results = await pool.query(
      `SELECT DISTINCT ${field} FROM sales WHERE ${field} ILIKE $1`,
      [`%${searchString}%`]
    );
  }
  return results.rows.map(row => row[field]);
};

module.exports = {
  getSales,
  addSales,
  updateSales,
  deleteSales,
  getSuggestions,
};
